﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class Information_DeliveryDAL
    {


        public static string conn = ConfigurationSettings.AppSettings["con"].ToString();

        /// <summary>
        /// 获取全部题目
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCanteen(string jh)
        {
            SqlParameter[] sp = new SqlParameter[1];
            sp[0] = new SqlParameter("@jh", jh);
            // string sql = "select  * from canteen";
            string sql = "";
            string where = "";

            sql = "select  * from questionnaire where 1=1";
            if (jh != "") { where += " and questionjh in (select jh from jhsz where SN=@jh)"; }
            sql += where;
            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
        }


        //查询显示图片
        public static string GetImages()
        {
            string sql = "select images From systemset";
            return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, null).ToString();
        }

        public static DataTable SelTitle_Publish(string jh)
        {
            SqlParameter[] sp = new SqlParameter[1];
            sp[0] = new SqlParameter("@jh", jh);
            // string sql = "select  * from canteen";
            string sql = "";
            string where = "";

            sql = "select * from Custom_Information where 1=1";
            if (jh != "") { where += " and Customjh in (select jh from jhsz where SN=@jh)"; }
            sql += where + " order by CustomCode desc";
            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
        }

        /// <summary>
        /// 校验题目
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static bool Canteen_Check_Name(string jh, string name)
        {
            bool falg = false;
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@jh",jh),
                new SqlParameter("@name",name)
            };
            try
            {
                string sql = " SELECT COUNT(*) FROM questionnaire WHERE questionName =@name and questionjh=@jh";
                int num = (Int32)SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, sp);
                if (num > 0)
                {
                    falg = true;
                }
                else
                {
                    falg = false;
                }
                return falg;
            }
            catch (Exception ex)
            {
                return falg;
            }
        }


        /// <summary>
        /// 获取题目
        /// </summary>
        /// <returns></returns>
        public static string questionnaire(string Code)
        {

            SqlParameter[] sp = new SqlParameter[1];
            sp[0] = new SqlParameter("@Code", Code);
            string sql = "select questionName From questionnaire where questionCode=@Code";

            return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, sp).ToString();
        }

        /// <summary>
        /// 问卷调查添加
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static int FindingsAdd(string questionCode, string questionjh, string questionName, string content, string Investigator)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@questionCode",questionCode),
                new SqlParameter("@questionjh",questionjh),
                new SqlParameter("@questionName",questionName),
                new SqlParameter("@content",content),
                new SqlParameter("@Investigator",Investigator)
            };
            try
            {
                string sql = @" insert into Findings values(@questionCode,@questionjh,@questionName,@content,GETDATE(),@Investigator) ";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp);
            }
            catch (Exception ex)
            {
                return -1;
            }

        }


        /// <summary>
        /// 菜品评价
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static int Increase_Dish_Evaluation(string satisfied, string summary)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@satisfied",satisfied),
                new SqlParameter("@summary",summary)
            };
            try
            {
                string sql = @" insert into Message_Board values(@satisfied,@summary,GETDATE()) ";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp);
            }
            catch (Exception ex)
            {
                return -1;
            }

        }


        /// <summary>
        /// 修改题目信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public static int ManageCanteenEdit(int id, string name, string jh)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Id",id),
                new SqlParameter("@name",name),
                new SqlParameter("@jh",jh)
            };
            try
            {
                string sql = "UPDATE questionnaire SET questionName=@name,questionjh=@jh WHERE questionCode=@Id";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp);
            }
            catch (Exception ex)
            {
                return -1;
            }
        }
        /// <summary>
        /// 删除题目
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static int Batch_Delete(int Id)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Id",Id)
            };
            try
            {
                string sql = "DELETE FROM questionnaire WHERE questionCode =@Id";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp);
            }
            catch (Exception ex)
            {
                return -1;
            }
        }
        /// <summary>
        /// 获取自定义名称
        /// </summary>
        /// <returns></returns>
        public static string SelCustom_Information()
        {
            string sql = "select Custom_Information from systemset";
            return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, null).ToString();
        }

        /// <summary>
        /// 自定义信息添加
        /// </summary>
        /// <returns></returns>
        public static string Custom_InformationAdd(string CustomContent, string images)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@CustomContent",CustomContent),
                new SqlParameter("@images",images)
            };
            string sql = "insert into Custom_Information values(@CustomContent,@images,GETDATE())";
            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp).ToString();
        }


        /// <summary>
        /// 获取全部题目
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCustomCode(string jh)
        {
            SqlParameter[] sp = new SqlParameter[1];
            sp[0] = new SqlParameter("@jh", jh);
            // string sql = "select  * from canteen";
            string sql = "";
            string where = "";

            sql = "select  * from Custom_Information where 1=1";
            // if (jh != "") { where += " and Customjh in (select jh from jhsz where SN=@jh)"; }
            if (jh != "") { where += " and CustomCode=@jh"; }
            sql += where;
            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
        }

    }
}
